﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Data.OleDb;   
using System.Text.RegularExpressions;   


namespace Exhibition.Common.Helper
{
    public class DBCommonHelper : IDisposable
    {
         
             
 
            #region public properties   
  
            /// <summary>   
            /// 连接字符串   
            /// </summary>   
            public string StrConnectionString   
            {   
                get { return _strConnectionString; }   
                set { _strConnectionString = value; }   
            }   
  
            /// <summary>   
            /// 数据库类型   
            /// </summary>   
            public enum DbType   
            {   
                Unknown = 0,   
                MySql = 1,   
                Sqlite = 2,   
                MsSqlServer = 3,   
                Oracle = 4   
            }  
 
            #endregion  
 
            #region public methods  
 
            #region 构造函数   
  
            /// <summary>   
            /// 构造函数   
            /// 从配置文件中读取数据库类型、连接字符串   
            /// </summary>   
            protected DBCommonHelper()   
            {   
                _strDbType = "";   
                if (Conn != null) return;   
                //数据库类型   
                _strDbType = ConfigurationManager.AppSettings["DBType"];   
                //数据库连接字符串   
                _strConnectionString = ConfigurationManager.AppSettings["ConnectionString"];   
                //设置连接字符   
                SetConnection(_strDbType, _strConnectionString);   
            }   
  
            /// <summary>   
            /// 构造函数   
            /// </summary>  
            /// <param name="strDbType">数据库类型:"System.Data.SqlClient" or "System.Data.OracleClient" or "System.Data.Odbc" or "System.Data.OleDb"</param>  
            /// <param name="strConn">连接字符串</param>  
            protected DBCommonHelper(string strDbType)   
            {   
                _strDbType = strDbType;   
            }   
  
            /// <summary>   
            /// 构造函数   
            /// </summary>   
            /// <param name="strDbType">数据库类型:"System.Data.SqlClient" or "System.Data.OracleClient" or "System.Data.Odbc" or "System.Data.OleDb"</param>   
            /// <param name="strConn">连接字符串</param>   
            public DBCommonHelper(string strDbType, string strConn)   
            {   
                //获取连接   
                SetConnection(strDbType, strConn);   
            }   
  
            /// <summary>   
            /// 构造函数   
            /// </summary>   
            /// <param name="strDBType">数据库类型:"System.Data.SqlClient" or "System.Data.OracleClient" or "System.Data.Odbc" or "System.Data.OleDb"</param>   
            /// <param name="strConn">连接字符串</param>   
            /// <param name="cmdText">查询语句</param>   
            protected DBCommonHelper(string strDBType, string strConn, string cmdText)   
            {   
                _strDbType = "";   
                //获取连接   
                SetConnection(strDBType, strConn);   
                //获取数据库命令接口   
                _iCmd = GetCommand(cmdText);   
            }   
  
            /// <summary>   
            /// 设置连接   
            /// </summary>   
            /// <param name="strDbType">数据库类型:"System.Data.SqlClient" or "System.Data.OracleClient" or "System.Data.Odbc" or "System.Data.OleDb,MySql.Data.MySqlClient"</param>   
            /// <param name="strConn">连接字符串</param>   
            public void SetConnection(string strDbType, string strConn)   
            {   
                _strDbType = strDbType;   
                  
                if (!string.IsNullOrEmpty(strDbType))   
                {   
                    //获取指定提供程序名称的 DbProviderFactory 的一个实例   
                    _dbFactory = DbProviderFactories.GetFactory(strDbType);   
                  
                    //创建连接   
                    Conn = _dbFactory.CreateConnection();   
                    //连接字符串   
                    if (!string.IsNullOrEmpty(strConn)) Conn.ConnectionString = strConn;   
                }   
            }  
 
            #endregion  
 
            #region 获取数据库连接接口   
  
            /// <summary>   
            /// 获取数据库连接接口   
            /// </summary>   
            public IDbConnection Conn { get; private set; }  
 
            #endregion   
  
            /// <summary>   
            /// 获取当前数据库类型   
            /// </summary>   
            /// <returns></returns>   
            public DbType GetDbType()   
            {   
                switch (_strDbType.ToUpper())   
                {   
                    case "MYSQL.DATA.MYSQLCLIENT":   
                        return DbType.MySql;   
                    case "SYSTEM.DATA.SQLCLIENT":   
                        return DbType.MsSqlServer;   
                    case "SYSTEM.DATA.ORACLECLIENT":   
                        return DbType.Oracle;   
                    default:   
                        return DbType.Unknown;   
                }   
            }  
 
            #region 获取事务   
  
            /// <summary>   
            /// 获取事务   
            /// </summary>   
            /// <returns></returns>   
            public IDbTransaction GetTransaction()   
            {   
                return Conn.BeginTransaction();   
            }  
 
            #endregion  
 
            #region 打开连接   
  
            /// <summary>   
            /// 打开连接   
            /// </summary>   
            public void Open()   
            {   
                if (Conn.State == ConnectionState.Closed)   
                {   
                    //关闭状态，打开连接   
                    Conn.Open();   
                }   
                else if (Conn.State == ConnectionState.Broken)   
                {   
                    //中断状态，先关闭后打开连接   
                    Conn.Close();   
                    Conn.Open();   
                }   
            }  
 
            #endregion  
 
            #region 关闭连接   
  
            /// <summary>   
            /// 关闭连接   
            /// </summary>   
            public void Close()   
            {   
                if (Conn == null)   
                {   
                    return;   
                }   
  
                if (Conn.State != ConnectionState.Closed)   
                {   
                    Conn.Close();   
                }   
            }  
 
            #endregion  
 
            #region 获取数据库命令接口   
  
            /// <summary>   
            /// 获取数据库命令接口   
            /// </summary>   
            /// <param name="cmdText">查询语句</param>   
            /// <returns>返回数据库命令接口</returns>   
            public IDbCommand GetCommand(string cmdText)   
            {   
                //创建数据库命令   
                IDbCommand cmd = _dbFactory.CreateCommand();   
                //设置数据源运行的文本命令   
                cmd.CommandText = cmdText;   
                //设置连接   
                cmd.Connection = Conn;   
                return cmd;   
            }   
  
            /// <summary>   
            /// 获取数据库命令接口   
            /// </summary>   
            /// <param name="cmdText">查询语句</param>   
            /// <param name="conn">数据库连接接口</param>   
            /// <returns>返回数据库命令接口</returns>   
            public IDbCommand GetCommand(string cmdText, IDbConnection conn)   
            {   
                IDbCommand cmd = _dbFactory.CreateCommand();   
                cmd.CommandText = cmdText;   
                cmd.Connection = conn;   
                return cmd;   
            }  
 
            #endregion  
 
            #region 执行SQL语句返回数据行   
  
            /// <summary>   
            /// 执行SQL语句返回数据行   
            /// </summary>   
            /// <param name="cmdText">查询语句</param>   
            /// <returns>返回数据读取器接口</returns>   
            public IDataReader GetDataReader(string cmdText)   
            {   
                if (_idr != null)   
                {   
                    _idr.Close();   
                }   
  
                Open(); //连接数据库   
  
                _iCmd = GetCommand(cmdText);   
                _idr = _iCmd.ExecuteReader();   
  
                return _idr;   
            }   
  
            /// <summary>   
            /// 执行SQL语句返回数据行   
            /// </summary>   
            /// <param name="tran">事务</param>   
            /// <param name="strSql">查询语句</param>   
            /// <returns>返回数据读取器接口</returns>   
            public IDataReader GetDataReader(IDbTransaction tran, string strSql)   
            {   
                if (_idr != null)   
                {   
                    _idr.Close();   
                }   
                try  
                {   
                    Open();   
                    _iCmd = GetCommand(strSql);   
                    _iCmd.Transaction = tran;   
                    _idr = _iCmd.ExecuteReader();   
                    tran.Commit();   
                }   
                catch  
                {   
                    tran.Rollback();   
                    throw;   
                }   
  
                return _idr;   
            }  
 
            #endregion  
 
            #region 执行SQL语句返回受影响的行数   
  
            /// <summary>   
            /// 执行SQL语句返回受影响的行数   
            /// </summary>   
            /// <param name="cmdText">查询语句</param>   
            /// <returns>返回受影响的行数(-1:执行失败)</returns>   
            public int ExecuteNonQuery(string cmdText)   
            {   
                int iRows;   
                try  
                {   
                    Open();   
                    _iCmd = GetCommand(cmdText);   
                    iRows = _iCmd.ExecuteNonQuery();   
                }   
                finally  
                {   
                    Close();   
                }   
                return iRows;   
            }   
  
            /// <summary>   
            /// 执行SQL语句返回受影响的行数   
            /// </summary>   
            /// <param name="cmdText">查询语句</param>   
            /// <param name="ps"></param>   
            /// <returns>返回受影响的行数(-1:执行失败)</returns>   
            public int ExecuteNonQuery(string cmdText, params IDbDataParameter[] ps)   
            {   
                int iRows;   
                try  
                {   
                    Open();   
                    _iCmd = GetCommand(cmdText);   
                    if (ps != null)   
                    {   
                        _iCmd.Parameters.Clear();   
                        foreach (IDbDataParameter p in ps)   
                        {   
                            _iCmd.Parameters.Add(p);   
                        }   
                    }   
                    iRows = _iCmd.ExecuteNonQuery();   
                }   
                finally  
                {   
                    Close();   
                }   
                return iRows;   
            }   
  
            /// <summary>   
            /// 执行SQL语句返回受影响的行数   
            /// </summary>   
            /// <param name="cmdText">查询语句</param>   
            /// <param name="paraNames"></param>   
            /// <returns>返回受影响的行数(-1:执行失败)</returns>   
            public int ExecuteNonQuery(string cmdText, string[] paraNames)   
            {   
                return ExecuteNonQuery(cmdText, paraNames, new object[] { });   
            }   
  
            /// <summary>   
            /// 执行SQL语句返回受影响的行数   
            /// </summary>   
            /// <param name="cmdText">查询语句</param>   
            /// <param name="paraNames"></param>   
            /// <param name="paraValues"></param>   
            /// <returns>返回受影响的行数(-1:执行失败)</returns>   
            public int ExecuteNonQuery(string cmdText, string[] paraNames, object[] paraValues)   
            {   
                int iRows;   
                try  
                {   
                    Open();   
                    _iCmd = GetCommand(cmdText);   
                    if (paraNames != null && paraValues != null)   
                    {   
                        _iCmd.Parameters.Clear();   
                        for (int i = 0; i < paraNames.Length; i++)   
                        {   
                            IDbDataParameter p = CreateParam(paraNames[i], paraValues[i]);   
  
                            _iCmd.Parameters.Add(p);   
                        }   
                    }   
                    iRows = _iCmd.ExecuteNonQuery();   
                }   
                finally  
                {   
                    Close();   
                }   
                return iRows;   
            }  
 
            #endregion  
 
            #region 执行SQL语句返回数据集   
  
            /// <summary>   
            /// 执行SQL语句返回数据集   
            /// </summary>   
            /// <param name="cmdText">查询语句</param>   
            /// <returns>返回数据集</returns>   
            public DataSet GetDataSet(string cmdText)   
            {   
                var ds = new DataSet();   
                try  
                {   
                    Open();   
                    _ida = _dbFactory.CreateDataAdapter();   
                    _ida.SelectCommand = GetCommand(cmdText);   
                    _ida.Fill(ds);   
                }   
                finally  
                {   
                    Close();   
                }   
                return ds;   
            }   
  
  
            /// <summary>   
            /// 执行SQL语句返回数据集   
            /// </summary>   
            /// <param name="cmdText">查询语句</param>   
            /// <param name="conn">数据库连接接口</param>   
            /// <returns>返回数据集</returns>   
            public DataSet GetDataSet(string cmdText, IDbConnection conn)   
            {   
                var ds = new DataSet();   
                try  
                {   
                    Open();   
                    _ida = _dbFactory.CreateDataAdapter();   
                    _ida.SelectCommand = GetCommand(cmdText, conn);   
                    _ida.Fill(ds);   
                }   
                catch  
                {   
                    throw;   
                }   
                finally  
                {   
                    Close();   
                }   
                return ds;   
            }   
  
  
            /// <summary>   
            /// 执行带参数SQL语句返回数据集   
            /// </summary>     
            /// <param name="strSql"></param>   
            /// <param name="param">SQL语句参数</param>   
            /// <returns>返回数据集</returns>   
            public DataSet GetDataSet(string strSql, params IDbDataParameter[] param)   
            {   
                return GetDataSet(CommandType.Text, strSql, param);   
            }   
  
            /// <summary>   
            /// 通过参数获取集据集   
            /// </summary>   
            /// <param name="strSql"></param>   
            /// <param name="paramNames"></param>   
            /// <param name="paramValues"></param>   
            /// <returns></returns>   
            public DataSet GetDataSet(string strSql, string[] paramNames, object[] paramValues)   
            {   
                return GetDataSet(CommandType.Text, strSql, paramNames, paramValues);   
            }   
  
            /// <summary>   
            /// 执行存储过程返回数据集   
            /// </summary>   
            /// <param name="cmdType">命令类型</param>   
            /// <param name="strProcudureName">存储过程名称</param>   
            /// <param name="param">存储过程参数</param>   
            /// <returns>返回数据集</returns>   
            public DataSet GetDataSet(CommandType cmdType, string strProcudureName, params IDbDataParameter[] param)   
            {   
                var ds = new DataSet();   
                Open();   
                try  
                {   
                    _iCmd.Connection = Conn;   
                    _iCmd.CommandText = strProcudureName;   
                    _iCmd.CommandType = cmdType;   
                    if (param != null)   
                    {   
                        _iCmd.Parameters.Clear();   
                        foreach (DbParameter p in param)   
                        {   
                            _iCmd.Parameters.Add(p);   
                        }   
                    }   
                    _ida = _dbFactory.CreateDataAdapter();   
                    _ida.SelectCommand = _iCmd;   
                    _ida.Fill(ds);   
                }   
                finally  
                {   
                    Close();   
                }   
                return ds;   
            }   
  
            /// <summary>   
            /// 通过参数查询数据集   
            /// </summary>   
            /// <param name="cmdType">执行类型</param>   
            /// <param name="strProcudureName">执行语句</param>   
            /// <param name="paramNames">参数名集合</param>   
            /// <param name="paramValues">参数值集合</param>   
            /// <returns></returns>   
            public DataSet GetDataSet(CommandType cmdType, string strProcudureName, string[] paramNames,   
                                      object[] paramValues)   
            {   
                var ds = new DataSet();   
                Open();   
                try  
                {   
                    _iCmd.Connection = Conn;   
                    _iCmd.CommandText = strProcudureName;   
                    _iCmd.CommandType = cmdType;   
                    if (paramNames != null)   
                    {   
                        _iCmd.Parameters.Clear();   
                        for (int i = 0; i < paramNames.Length; i++)   
                        {   
                            IDbDataParameter p = CreateParam(paramNames[i], paramValues[i]);   
                            _iCmd.Parameters.Add(p);   
                        }   
                    }   
  
                    _ida = _dbFactory.CreateDataAdapter();   
                    _ida.SelectCommand = _iCmd;   
                    _ida.Fill(ds);   
                }   
                finally  
                {   
                    Close();   
                }   
                return ds;   
            }  
 
 
            #endregion  
 
            #region 执行SQL语句返回单值对象   
  
            /// <summary>   
            /// 执行SQL语句返回单值对象   
            /// </summary>   
            /// <param name="cmdText">查询语句</param>   
            /// <returns>返回结果集中第一行的第一列数据</returns>   
            public object ExecuteScalar(string cmdText)   
            {   
                try  
                {   
                    Open();   
                    _iCmd = GetCommand(cmdText);   
                    return _iCmd.ExecuteScalar();   
                }   
                finally  
                {   
                    Close();   
                }   
            }   
  
            /// <summary>   
            /// 通过参数获取值   
            /// </summary>   
            /// <param name="cmdText"></param>   
            /// <param name="paramNames"></param>   
            /// <param name="paramValues"></param>   
            /// <returns></returns>   
            public object ExecuteScalar(string cmdText, string[] paramNames, object[] paramValues)   
            {   
                try  
                {   
                    Open();   
                    _iCmd = GetCommand(cmdText);   
  
                    _iCmd.Parameters.Clear();   
                    if (paramNames != null)   
                    {   
                        for (int i = 0; i < paramNames.Length; i++)   
                        {   
                            IDbDataParameter p = CreateParam(paramNames[i], paramValues[i]);   
                            _iCmd.Parameters.Add(p);   
                        }   
                    }   
  
                    return _iCmd.ExecuteScalar();   
                }   
                finally  
                {   
                    Close();   
                }   
            }   
  
            /// <summary>   
            /// 执行SQL语句返回单值对象   
            /// </summary>   
            /// <param name="cmdText">查询语句</param>   
            /// <param name="conn">数据库连接接口</param>   
            /// <returns>返回结果集中第一行的第一列数据</returns>   
            public object ExecuteScalar(string cmdText, IDbConnection conn)   
            {   
                try  
                {   
                    Open();   
                    _iCmd = GetCommand(cmdText, conn);   
                    return _iCmd.ExecuteScalar();   
                }   
                finally  
                {   
                    Close();   
                }   
            }  
 
            #endregion  
 
            #region 执行多条SQL语句   
  
            /// <summary>   
            /// 执行多条SQL语句   
            /// </summary>   
            /// <param name="strSql">SQL语句</param>   
            /// <returns>-1:不成功;1:成功</returns>   
            public int ExecuteSql(string[] strSql)   
            {   
                //是否成功标识   
                int iSuccess = -1;   
                Open();   
                try  
                {   
                    _iTran = GetTransaction();   
                    int iLength = strSql.Length;   
                    for (int i = 0; i < iLength; i++)   
                    {   
                        _iCmd = GetCommand(strSql[i]);   
                        _iCmd.Transaction = _iTran;   
                        _iCmd.ExecuteNonQuery();   
                    }   
                    _iTran.Commit();   
                    iSuccess = 1;   
                }   
                catch  
                {   
                    _iTran.Rollback();   
                    throw;   
                }   
                finally  
                {   
                    Close();   
                }   
                return iSuccess;   
            }   
  
            /// <summary>   
            /// 执行SQL语句集合   
            /// </summary>   
            /// <param name="strSql"></param>   
            /// <returns></returns>   
            public int ExecuteSql(List<string> strSql)   
            {   
                //是否成功标识   
                int iSuccess = 0;   
                Open();   
                try  
                {   
                    _iTran = GetTransaction();   
                    for (int i = 0; i < strSql.Count; i++)   
                    {   
                        _iCmd = GetCommand(strSql[i]);   
                        _iCmd.Transaction = _iTran;   
  
                        iSuccess += _iCmd.ExecuteNonQuery();   
                    }   
  
                    _iTran.Commit();   
                }   
                catch  
                {   
                    _iTran.Rollback();   
                    throw;   
                }   
                finally  
                {   
                    Close();   
                }   
                return iSuccess;   
            }   
  
            /// <summary>   
            /// 执行多条SQL语句   
            /// </summary>   
            /// <param name="strSql">SQL语句</param>   
            /// <returns>-1:不成功;1:成功</returns>   
            public int ExecuteSql(List<string> strSql, string[][] paraNames, object[][] paraValues)   
            {   
                //是否成功标识   
                int iSuccess = 0;   
                Open();   
                try  
                {   
                    _iTran = GetTransaction();   
                    for (int i = 0; i < strSql.Count; i++)   
                    {   
                        _iCmd = GetCommand(strSql[i]);   
                        _iCmd.Transaction = _iTran;   
  
                        _iCmd.Parameters.Clear();   
                        for (int j = 0; j < paraNames[i].Length; j++)   
                        {   
                            IDbDataParameter p = CreateParam(paraNames[i][j], paraValues[i][j]);   
  
                            _iCmd.Parameters.Add(p);   
                        }   
  
                        iSuccess += _iCmd.ExecuteNonQuery();   
                    }   
  
                    _iTran.Commit();   
                }   
                catch  
                {   
                    _iTran.Rollback();   
                    throw;   
                }   
                finally  
                {   
                    Close();   
                }   
                return iSuccess;   
            }  
 
            #endregion   
  
            /// <summary>   
            /// 获取所有表名   
            /// </summary>   
            /// <returns></returns>   
            public List<string> GetAllTableName()   
            {   
                var tableNames = new List<string>();   
                var oledb = Conn as OleDbConnection;   
                if (oledb != null)   
                {   
                    oledb.Open();   
                    DataTable dt = oledb.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);   
  
                    foreach (DataRow dr in dt.Rows)   
                    {   
                        tableNames.Add(dr[2].ToString());   
                    }   
                }   
  
                return tableNames;   
            }   
  
            /// <summary>   
            /// 替换SQL关健字符   
            /// </summary>   
            /// <param name="source"></param>   
            /// <returns></returns>   
            public static string ReplaceSqlChar(string source)   
            {   
                if (string.IsNullOrEmpty(source)) return source;   
  
                var reg = new Regex(@"\%|'|=|_|\*| or |\?|<|>", RegexOptions.IgnoreCase);   
  
                return reg.Replace(source, "");   
            }   
  
            /// <summary>   
            /// 生成参数对象   
            /// </summary>   
            /// <param name="param"></param>   
            /// <param name="obj"></param>   
            /// <returns></returns>   
            private IDbDataParameter CreateParam(string param, object obj)   
            {   
                IDbDataParameter p = _dbFactory.CreateParameter();                  
                p.ParameterName = param;   
                p.Value = obj;   
                return p;   
            }   
  
            /// <summary>   
            /// 释放资源   
            /// </summary>   
            public void Dispose()   
            {   
                Dispose(true);   
            }  
 
            #endregion  
 
            #region event handlers  
 
            #endregion  
 
            #region private field   
  
            //数据连接接口   
            //数据命令接口   
            private IDbCommand _iCmd;   
            //数据适配器接口   
            private IDbDataAdapter _ida;   
            //数据读取器接口   
            private IDataReader _idr;   
            //事务接口   
            private IDbTransaction _iTran;   
            //数据提供程序   
            private DbProviderFactory _dbFactory;   
            //数据库类型   
            private string _strDbType;   
            //数据库连接字符串   
            private string _strConnectionString;  
 
            #endregion  
 
            #region private methods   
  
            /// <summary>   
            /// 释放资源   
            /// </summary>   
            /// <param name="disposing"></param>   
            private void Dispose(bool disposing)   
            {   
                if (disposing)   
                {   
                    if (Conn != null)   
                    {   
                        Conn.Dispose();   
                    }   
  
                    GC.SuppressFinalize(true);   
                }   
            }  
 
            #endregion   
        }   
    }   
    